# 过滤适合买入的股票
from datetime import datetime
import os
import sqlite3
import akshare as ak
import pandas as pd

from datacache import table_exists

conn = sqlite3.connect(os.path.join(os.getcwd(), 'stocks_runtime_data_cache.db'), check_same_thread=False)

def get_filtered_stock(runtime):
    price_high_limit = 150
    price_low_limit = 20
    market_value_high_limit = 20000000000
    market_value_low_limit = 2000000000

    today_date = datetime.now().strftime('%Y%m%d')
    table_name = f"stock_spot_{today_date}"
    is_table_exist = table_exists(table_name)
    if runtime:
        # 如果要获取实时行情，直接返回
        runtimedf = get_stock_spot_data(True)
        runtimedf.fillna(0)
        condition = (
                (runtimedf['流通市值'] >= market_value_low_limit) &
                (runtimedf['流通市值'] <= market_value_high_limit) &
                (runtimedf['昨收'] >= price_low_limit) &
                (runtimedf['昨收'] <= price_high_limit) &
                (runtimedf['代码'].str.startswith(('00', '60')))
        )
        return runtimedf[condition]
    if not is_table_exist:
        get_stock_spot_data(False)
    try:
        # 连接到 SQLite 数据库
        query = f"""
            SELECT *
            FROM {table_name}
            WHERE 昨收 BETWEEN ? AND ?
            AND 流通市值 BETWEEN ? AND ?
            AND (代码 LIKE '60%' OR 代码 LIKE '00%')
            """
        # 使用 pandas 读取 SQL 查询结果
        df = pd.read_sql_query(query, conn, params=(price_low_limit, price_high_limit, market_value_low_limit, market_value_high_limit))
        return df
    except sqlite3.OperationalError:
        print(f"No cached data found.")
        return None

def get_stock_spot_data(runtime):
    stock_detail_data = ak.stock_zh_a_spot_em()
    if runtime:
        return stock_detail_data
    today_date = datetime.now().strftime('%Y%m%d')
    table_name = f"stock_spot_{today_date}"
    stock_detail_data.to_sql(table_name, conn, if_exists='replace', index=False)

if __name__ == "__main__":
    res = get_filtered_stock(True)
    print(res)